package org.jeecg.modules.finance.mapper;

import cn.hutool.core.date.DateTime;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.jeecg.modules.business.entity.BusRoomPayType;
import org.jeecg.modules.finance.vo.RoomStatVo;
import org.jeecg.modules.kc.entity.KcDepositoryInGoods;
import org.jeecg.modules.pos.entity.PosOrderGoodsPayment;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

/**
 * @Description: 收款汇总统计
 * @Author: jeecg-boot
 * @Date:   2023-04-19
 * @Version: V1.0
 */
public interface SummaryMapper extends BaseMapper<PosOrderGoodsPayment> {

    /**
     * 收款汇总统计全部
     *
     * @param page
     * @param busRoomPayTypes
     * @param hotelId
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("<script>select max(h.name) as hotel_name,'POS' as department\n" +
            "<foreach item='item' index='index' collection='busRoomPayTypes' open=',' separator=',' close=' '>" +
            "SUM(case ogp.payment_method when '${item.id}' then ogp.pay_money else 0 end) as '${item.name}'\n" +
            "</foreach>" +
            "from bus_hotel_info h\n" +
            "inner join bus_room_pay_type_info pty on h.id=pty.hotel_id\n" +
            "left join pos_order_goods_payment ogp on pty.id=ogp.payment_method\n" +
            "where 1=1" +
            "<if test='hotelId != null and hotelId !=\"\"'> and h.id = '${hotelId}' </if>" +
            "<if test='startTime != null'> and ogp.create_time &gt;= '${startTime}' </if>" +
            "<if test='endTime != null'> and ogp.create_time &lt;= '${endTime}' </if>" +
            "group by h.id\n" +
            "UNION ALL\n" +
            "select max(h.name) as hotel_name,'客房' as department\n" +
            "<foreach item='item' index='index' collection='busRoomPayTypes' open=',' separator=',' close=' '>" +
            "SUM(case of.pay_type when '${item.id}' then of.money else 0 end) as '${item.name}'\n" +
            "</foreach>" +
            "from bus_hotel_info h\n" +
            "inner join bus_room_pay_type_info pty on h.id=pty.hotel_id\n" +
            "left join bus_order_fee of on pty.id=of.pay_type\n" +
            "where of.fee_type=2 and of.preferential_status=2" +
            "<if test='hotelId != null and hotelId !=\"\"'> and h.id = '${hotelId}' </if>" +
            "<if test='startTime != null'> and of.create_time &gt;= '${startTime}' </if>" +
            "<if test='endTime != null'> and of.create_time &lt;= '${endTime}' </if>" +
            "group by h.id</script>")
    public List<HashMap<String, Object>> pageList(Page<HashMap<String, Object>> page, @Param("busRoomPayTypes") List<BusRoomPayType> busRoomPayTypes, @Param("hotelId") String hotelId, @Param("startTime") DateTime startTime, @Param("endTime") DateTime endTime);


    /**
     * 收款汇总统计POS
     *
     * @param page
     * @param busRoomPayTypes
     * @param hotelId
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("<script>select max(h.name) as hotel_name,'POS' as department\n" +
            "<foreach item='item' index='index' collection='busRoomPayTypes' open=',' separator=',' close=' '>" +
            "SUM(case ogp.payment_method when '${item.id}' then ogp.pay_money else 0 end) as '${item.name}'\n" +
            "</foreach>" +
            "from bus_hotel_info h\n" +
            "inner join bus_room_pay_type_info pty on h.id=pty.hotel_id\n" +
            "left join pos_order_goods_payment ogp on pty.id=ogp.payment_method\n" +
            "where 1=1" +
            "<if test='hotelId != null and hotelId !=\"\"'> and h.id = '${hotelId}' </if>" +
            "<if test='startTime != null'> and ogp.create_time &gt;= '${startTime}' </if>" +
            "<if test='endTime != null'> and ogp.create_time &lt;= '${endTime}' </if>" +
            "group by h.id</script>")
    public List<HashMap<String, Object>> posPageList(Page<HashMap<String, Object>> page, @Param("busRoomPayTypes") List<BusRoomPayType> busRoomPayTypes, @Param("hotelId") String hotelId, @Param("startTime") DateTime startTime, @Param("endTime") DateTime endTime);

    /**
     * 收款汇总统计客房
     *
     * @param page
     * @param busRoomPayTypes
     * @param hotelId
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("<script>select max(h.name) as hotel_name,'客房' as department\n" +
            "<foreach item='item' index='index' collection='busRoomPayTypes' open=',' separator=',' close=' '>" +
            "SUM(case of.pay_type when '${item.id}' then of.money else 0 end) as '${item.name}'\n" +
            "</foreach>" +
            "from bus_hotel_info h\n" +
            "inner join bus_room_pay_type_info pty on h.id=pty.hotel_id\n" +
            "left join bus_order_fee of on pty.id=of.pay_type\n" +
            "where of.fee_type=2 and of.preferential_status=2" +
            "<if test='hotelId != null and hotelId !=\"\"'> and h.id = '${hotelId}' </if>" +
            "<if test='startTime != null'> and of.create_time &gt;= '${startTime}' </if>" +
            "<if test='endTime != null'> and of.create_time &lt;= '${endTime}' </if>" +
            "group by h.id</script>")
    public List<HashMap<String, Object>> roomPageList(Page<HashMap<String, Object>> page, @Param("busRoomPayTypes") List<BusRoomPayType> busRoomPayTypes, @Param("hotelId") String hotelId, @Param("startTime") DateTime startTime, @Param("endTime") DateTime endTime);


    /**
     * 收退款明细列表
     *
     * @param page
     * @param hotelId
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("<script>select 'POS' as department,pty.name as payment_method_name,'收款' as fee_type,ogp.order_id,'--' as contact_name,'--' as room_name,ogp.pay_money\n" +
            ",ogp.create_time\n" +
            "from pos_order_goods_payment ogp \n" +
            "left join bus_room_pay_type_info pty on pty.id=ogp.payment_method\n" +
            "where 1=1" +
            "<if test='hotelId != null and hotelId !=\"\"'> and ogp.hotel_id = #{hotelId} </if>" +
            "<if test='startTime != null'> and ogp.create_time &gt;= '${startTime}' </if>" +
            "<if test='endTime != null'> and ogp.create_time &lt;= '${endTime}' </if>" +
            "UNION ALL\n" +
            "select '住客' as department,pty.name as payment_method_name,\n" +
            "case when of.money>=0 then '收款' else '退款' end as fee_type,\n" +
            "of.living_order_id as order_id,c.name as contact_name,r.name as room_name,of.money as pay_money,of.create_time\n" +
            "from bus_order_fee of \n" +
            "left join bus_room_pay_type_info pty on pty.id=of.pay_type\n" +
            "left join bus_rooms_living_order rlo on rlo.id= of.living_order_id\n" +
            "left join ces_rooms r on r.id=of.room_id\n" +
            "left join bus_customer c on c.id=rlo.contact_id\n" +
            "where of.fee_type=2 and of.preferential_status=2" +
            "<if test='hotelId != null and hotelId !=\"\"'> and pty.hotel_id = #{hotelId} </if>" +
            "<if test='startTime != null'> and of.create_time &gt;= '${startTime}' </if>" +
            "<if test='endTime != null'> and of.create_time &lt;= '${endTime}' </if> " +
            "order by create_time desc" +
            "</script>")
    public List<HashMap<String, Object>> financePage(Page<HashMap<String, Object>> page, @Param("hotelId") String hotelId, @Param("startTime") DateTime startTime, @Param("endTime") DateTime endTime);


    /**
     * POS收退款明细列表
     *
     * @param page
     * @param hotelId
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("<script>select 'POS' as department,pty.name as payment_method_name,'收款' as fee_type,ogp.order_id,'--' as contact_name,'--' as room_name,ogp.pay_money\n" +
            ",ogp.create_time\n" +
            "from pos_order_goods_payment ogp \n" +
            "left join bus_room_pay_type_info pty on pty.id=ogp.payment_method\n" +
            "where 1=1" +
            "<if test='hotelId != null and hotelId !=\"\"'> and ogp.hotel_id = #{hotelId} </if>" +
            "<if test='startTime != null'> and ogp.create_time &gt;= #{startTime} </if>" +
            "<if test='endTime != null'> and ogp.create_time &lt;= #{endTime} </if> " +
            "order by ogp.create_time desc " +
            "</script>")
    public List<HashMap<String, Object>> postFinancePage(Page<HashMap<String, Object>> page, @Param("hotelId") String hotelId, @Param("startTime") DateTime startTime, @Param("endTime") DateTime endTime);

    /**
     * 住客收退款明细列表
     *
     * @param page
     * @param hotelId
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("<script>select '住客' as department,pty.name as payment_method_name,\n" +
            "case when of.money>=0 then '收款' else '退款' end as fee_type,\n" +
            "of.living_order_id as order_id,c.name as contact_name,r.name as room_name,of.money as pay_money,of.create_time\n" +
            "from bus_order_fee of \n" +
            "left join bus_room_pay_type_info pty on pty.id=of.pay_type\n" +
            "left join bus_rooms_living_order rlo on rlo.id= of.living_order_id\n" +
            "left join ces_rooms r on r.id=of.room_id\n" +
            "left join bus_customer c on c.id=rlo.contact_id\n" +
            "where of.fee_type=2 and of.preferential_status=2" +
            "<if test='hotelId != null and hotelId !=\"\"'> and pty.hotel_id = #{hotelId} </if>" +
            "<if test='startTime != null'> and of.create_time &gt;= #{startTime} </if>" +
            "<if test='endTime != null'> and of.create_time &lt;= #{endTime} </if> " +
            "order by create_time desc" +
            "</script>")
    public List<HashMap<String, Object>> roomFinancePage(Page<HashMap<String, Object>> page, @Param("hotelId") String hotelId, @Param("startTime") DateTime startTime, @Param("endTime") DateTime endTime);

    /**
     * 按时间每天收入统计列表
     *
     * @param hotelId
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("<script>select time,sum(room_money) as room_money,sum(other_money) as other_money from \n" +
            "(SELECT 'pos' as type, a.time\n" +
            ",0 AS room_money,\n" +
            "sum(ifnull(b.pay_money,0)) as other_money\n" +
            "FROM (\n" +
            "\tselect time from\n" +
            "\t(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) AS time from\n" +
            "\t (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,\n" +
            "\t (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,\n" +
            "\t (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,\n" +
            "\t (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,\n" +
            "\t (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v\n" +
//            "\twhere time between #{startTime} and #{endTime}\n" +
            "\twhere time between DATE(#{startTime}) AND DATE(#{endTime}) \n" +

            "\tORDER BY time\n" +
            "\t) a\n" +
            "\tLEFT JOIN \n" +
            "\t( SELECT id,create_time,pay_money FROM pos_order_goods_payment where 1=1" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = #{hotelId} </if>" +
            ") b\n" +
            "\tON a.time =date(b.create_time)\n" +
            "GROUP BY a.time\t\n" +
            "UNION All\n" +
            "SELECT '客房' as type, a.time,\n" +
            "sum(ifnull((case b.subject_type when 1 then b.money when 2 then b.money when 3 then b.money else 0 end),0)) AS room_money,\n" +
            "sum(ifnull((case b.subject_type when 6 then b.money when 7 then b.money else 0 end),0)) AS other_money\n" +
            "FROM (\n" +
            "\tselect time from\n" +
            "\t(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) AS time from\n" +
            "\t (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,\n" +
            "\t (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,\n" +
            "\t (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,\n" +
            "\t (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,\n" +
            "\t (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v\n" +
//            "\twhere time between #{startTime} and #{endTime}\n" +
            "\twhere time between DATE(#{startTime}) AND DATE(#{endTime}) \n" +
            "\tORDER BY time\n" +
            "\t) a\n" +
            "\tLEFT JOIN \n" +
            "\t( SELECT * FROM bus_order_fee where fee_type=1 and preferential_status=2" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = #{hotelId} </if>" +
            ") b\n" +
            "\tON a.time =date(b.create_time)\n" +
            "GROUP BY a.time)t\n" +
            "group by time\t</script>")
    public List<HashMap<String, Object>> dayShouRuStatList(@Param("hotelId") String hotelId, @Param("startTime") DateTime startTime, @Param("endTime") DateTime endTime);

    /**
     * 按时间每月收入统计列表
     *
     * @param hotelId
     * @param startTime
     * @param endTime
     * @param months
     * @return
     */
    @Select("<script>select time,sum(room_money) as room_money,sum(other_money) as other_money from \n" +
            "(SELECT 'pos' as type, a.time\n" +
            ",0 AS room_money,\n" +
            "sum(ifnull(b.pay_money,0)) as other_money\n" +
            "FROM (\n" +
            "\tselect DATE_FORMAT(time, '%Y-%m' ) as time from\n" +
            "\t(select adddate('${startTime}',INTERVAL  t0.i MONTH ) AS time from\n" +
            "\t (select 0 i " +
            "<foreach item='item' index='index' collection='months' open=' union ' separator=' union ' close=' '>" +
            " select ${item}" +
            "</foreach>" +
            ") t0) v\n" +
            "\tORDER BY time\n" +
            "\t) a\n" +
            "\tLEFT JOIN \n" +
            "\t( SELECT id,create_time,pay_money FROM pos_order_goods_payment where 1=1" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = '${hotelId}' </if>" +
            ") b\n" +
            "\tON a.time =DATE_FORMAT(b.create_time, '%Y-%m' )\n" +
            "GROUP BY a.time\t\n" +
            "UNION ALL\n" +
            "SELECT '客房' as type, a.time,\n" +
            "sum(ifnull((case b.subject_type when 1 then b.money when 2 then b.money when 3 then b.money else 0 end),0)) AS room_money,\n" +
            "sum(ifnull((case b.subject_type when 6 then b.money when 7 then b.money else 0 end),0)) AS other_money\n" +
            "FROM (\n" +
            "\tselect DATE_FORMAT(time, '%Y-%m' ) as time from\n" +
            "\t(select adddate('${startTime}',INTERVAL  t0.i MONTH ) AS time from\n" +
            "\t (select 0 i " +
            "<foreach item='item' index='index' collection='months' open=' union ' separator=' union ' close=' '>" +
            " select ${item}" +
            "</foreach>" +
            ") t0) v\n" +
            "\tORDER BY time\n" +
            "\t) a\n" +
            "\tLEFT JOIN \n" +
            "\t( SELECT * FROM bus_order_fee where fee_type=1 and preferential_status=2\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = '${hotelId}' </if>" +
            "\t) b ON a.time =DATE_FORMAT(b.create_time, '%Y-%m' )\n" +
            "GROUP BY a.time)t\n" +
            "group by time\t</script>")
    public List<HashMap<String, Object>> monthShouRuStatList(@Param("hotelId") String hotelId, @Param("startTime") DateTime startTime, @Param("endTime") DateTime endTime, @Param("months") List<Integer> months);

    /**
     * 按时间每季度收入统计列表
     *
     * @param hotelId
     * @param startTime
     * @param endTime
     * @param quarters
     * @return
     */
    @Select("<script>select time,sum(room_money) as room_money,sum(other_money) as other_money from \n" +
            "(SELECT 'pos' as type, a.time\n" +
            ",0 AS room_money,\n" +
            "sum(ifnull(b.pay_money,0)) as other_money\n" +
            "FROM (\n" +
            "\tselect concat(DATE_FORMAT(time, '%Y' ),'年-',QUARTER(time),'季度') as time from\n" +
            "\t(select adddate('${startTime}',INTERVAL  t0.i*3 MONTH ) AS time from\n" +
            "\t (select 0 i " +
            "<foreach item='item' index='index' collection='quarters' open=' union ' separator=' union ' close=' '>" +
            " select ${item}" +
            "</foreach>" +
            ") t0) v\n" +
            "\tORDER BY time\n" +
            "\t) a\n" +
            "\tLEFT JOIN \n" +
            "\t( SELECT id,create_time,pay_money FROM pos_order_goods_payment where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = '${hotelId}' </if>" +
            " ) b" +
            "\tON a.time =concat(DATE_FORMAT(b.create_time, '%Y' ),'年-',QUARTER(b.create_time),'季度')\n" +
            "GROUP BY a.time\t\n" +
            "UNION ALL\n" +
            "SELECT '客房' as type, a.time,\n" +
            "sum(ifnull((case b.subject_type when 1 then b.money when 2 then b.money when 3 then b.money else 0 end),0)) AS room_money,\n" +
            "sum(ifnull((case b.subject_type when 6 then b.money when 7 then b.money else 0 end),0)) AS other_money\n" +
            "FROM (\n" +
            "\tselect concat(DATE_FORMAT(time, '%Y' ),'年-',QUARTER(time),'季度') as time from\n" +
            "\t(select adddate('${startTime}',INTERVAL  t0.i*3 MONTH ) AS time from\n" +
            "\t (select 0 i " +
            "<foreach item='item' index='index' collection='quarters' open=' union ' separator=' union ' close=' '>" +
            " select ${item}" +
            "</foreach>" +
            ") t0) v\n" +
            "\tORDER BY time\n" +
            "\t) a\n" +
            "\tLEFT JOIN \n" +
            "\t( SELECT * FROM bus_order_fee where fee_type=1 and preferential_status=2\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = '${hotelId}' </if>" +
            " ) b ON a.time =concat(DATE_FORMAT(b.create_time, '%Y' ),'年-',QUARTER(b.create_time),'季度')\n" +
            "GROUP BY a.time)t\n" +
            "group by time</script>")
    public List<HashMap<String, Object>> quarterShouRuStatList(@Param("hotelId") String hotelId, @Param("startTime") DateTime startTime, @Param("endTime") DateTime endTime, @Param("quarters") List<Integer> quarters);

    /**
     * 按时间每年收入统计列表
     *
     * @param hotelId
     * @param startTime
     * @param endTime
     * @param years
     * @return
     */
    @Select("<script>select time,sum(room_money) as room_money,sum(other_money) as other_money from \n" +
            "(SELECT 'pos' as type, a.time\n" +
            ",0 AS room_money,\n" +
            "sum(ifnull(b.pay_money,0)) as other_money\n" +
            "FROM (\n" +
            "\tselect concat(DATE_FORMAT(time, '%Y' ),'年') as time from\n" +
            "\t(select adddate('${startTime}',INTERVAL  t0.i YEAR ) AS time from\n" +
            "\t (select 0 i " +
            "<foreach item='item' index='index' collection='years' open=' union ' separator=' union ' close=' '>" +
            " select ${item}" +
            "</foreach>" +
            ") t0) v\n" +
            "\tORDER BY time\n" +
            "\t) a\n" +
            "\tLEFT JOIN \n" +
            "\t( SELECT id,create_time,pay_money FROM pos_order_goods_payment where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = '${hotelId}' </if>" +
            " ) b" +
            "\tON a.time =concat(DATE_FORMAT(b.create_time, '%Y' ),'年')\n" +
            "GROUP BY a.time\t\n" +
            "UNION ALL\n" +
            "SELECT '客房' as type, a.time,\n" +
            "sum(ifnull((case b.subject_type when 1 then b.money when 2 then b.money when 3 then b.money else 0 end),0)) AS room_money,\n" +
            "sum(ifnull((case b.subject_type when 6 then b.money when 7 then b.money else 0 end),0)) AS other_money\n" +
            "FROM (\n" +
            "\tselect concat(DATE_FORMAT(time, '%Y' ),'年') as time from\n" +
            "\t(select adddate('${startTime}',INTERVAL  t0.i YEAR) AS time from\n" +
            "\t (select 0 i " +
            "<foreach item='item' index='index' collection='years' open=' union ' separator=' union ' close=' '>" +
            " select ${item}" +
            "</foreach>" +
            ") t0) v\n" +
            "\tORDER BY time\n" +
            "\t) a\n" +
            "\tLEFT JOIN \n" +
            "\t( SELECT * FROM bus_order_fee where fee_type=1 and preferential_status=2\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = '${hotelId}' </if>" +
            " ) b ON a.time =concat(DATE_FORMAT(b.create_time, '%Y' ),'年')\n" +
            "GROUP BY a.time)t\n" +
            "group by time</script>")
    public List<HashMap<String, Object>> yearShouRuStatList(@Param("hotelId") String hotelId, @Param("startTime") DateTime startTime, @Param("endTime") DateTime endTime, @Param("years") List<Integer> years);

    /**
     * 按收款方式收入统计列表
     * @param hotelId
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("<script>select name,sum(room_money) as room_money,sum(other_money) as other_money from \n" +
            "(SELECT 'pos' as type, max(a.name) as name\n" +
            ",0 AS room_money,\n" +
            "sum(ifnull(b.pay_money,0)) as other_money\n" +
            "FROM bus_room_pay_type_info a\n" +
            "\tLEFT JOIN  pos_order_goods_payment b\n" +
            "\tON a.id=b.payment_method\n" +
            "<if test='startTime != null'> and b.create_time &gt;= #{startTime} </if>" +
            "<if test='endTime != null'> and b.create_time &lt;= #{endTime} </if>" +
            "where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and a.hotel_id = '${hotelId}' </if>" +
            "GROUP BY a.id\t\n" +
            "UNION ALL\n" +
            "SELECT '客房' as type,max(a.name) as name,\n" +
            "sum(ifnull((case b.subject_type when 1 then b.money when 2 then b.money when 3 then b.money when 5 then b.money else 0 end),0)) AS room_money,\n" +
            "sum(ifnull((case b.subject_type when 6 then b.money when 7 then b.money else 0 end),0)) AS other_money\n" +
            "FROM \n" +
            "\tbus_room_pay_type_info a\n" +
            "\tLEFT JOIN bus_order_fee b\n" +
            "\tON a.id=b.pay_type and b.fee_type=2 and b.preferential_status=2\n" +
            "<if test='startTime != null'> and b.create_time &gt;= #{startTime} </if>" +
            "<if test='endTime != null'> and b.create_time &lt;= #{endTime} </if>" +
            "where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and a.hotel_id = '${hotelId}' </if>" +
            "GROUP BY a.id)t\n" +
            "group by name</script>")
    public List<HashMap<String, Object>> payMethodStatList(@Param("hotelId") String hotelId, @Param("startTime") DateTime startTime, @Param("endTime") DateTime endTime);

    /**
     * 按房型收入统计列表
     * @param hotelId
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("<script>SELECT max(a.name) as name\n" +
            ",ifnull(max(t2.rz_count),0) AS rz_count\n" +
            ",ifnull(max(t3.room_count),0) AS room_count\n" +
            ",sum(ifnull((case t.subject_type when 1 then t.money when 2 then t.money when 3 then t.money when 5 then t.money else 0 end),0)) AS room_money\n" +
            ",sum(ifnull((case t.subject_type when 6 then t.money when 7 then t.money else 0 end),0)) AS other_money\n" +
            ",sum(ifnull((case t.subject_type when 1 then t.money when 2 then t.money when 3 then t.money when 5 then t.money when 6 then t.money when 7 then t.money else 0 end),0)) AS sum_money\n" +
            ",round( sum(ifnull((case t.subject_type when 1 then t.money when 2 then t.money when 3 then t.money when 5 then t.money else 0 end),0))/ifnull(max(t3.room_count),0),2) AS room_average_price\n" +
            "FROM ces_room_layout a\n" +
            "LEFT JOIN (select room.layout_id,b.money,b.subject_type,b.room_id from ces_rooms room\n" +
            "LEFT JOIN  bus_order_fee b ON room.id=b.room_id and b.fee_type=2 and b.preferential_status=2" +
            "<if test='startTime != null'> and b.create_time &gt;= #{startTime} </if>" +
            "<if test='endTime != null'> and b.create_time &lt;= #{endTime} </if>" +
            ") t\n" +
            "ON t.layout_id=a.id\n" +
            "LEFT JOIN (\n" +
            "select layout_id,count(0) rz_count from(\n" +
            "select max(room.layout_id) as layout_id from ces_rooms room\n" +
            "LEFT JOIN  bus_order_fee b ON room.id=b.room_id and b.fee_type=2 and b.preferential_status=2\n" +
            "<if test='startTime != null'> and b.create_time &gt;= #{startTime} </if>" +
            "<if test='endTime != null'> and b.create_time &lt;= #{endTime} </if>" +
            "group by b.living_order_id) tmp group by layout_id) t2\n" +
            "ON t2.layout_id=a.id\n" +
            "LEFT JOIN (\n" +
            "select layout_id,count(0) as room_count from ces_rooms room\n" +
            "group by room.layout_id) t3\n" +
            "ON t3.layout_id=a.id\n" +
            "where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and a.hotel_id = '${hotelId}' </if>" +
            "GROUP BY a.id\t</script>")
    public List<HashMap<String, Object>> layoutStatList(@Param("hotelId") String hotelId, @Param("startTime") DateTime startTime, @Param("endTime") DateTime endTime);

    /**
     * 按费项收入统计列表
     * @param hotelId
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("<script>select '点餐' as name"+
            "<foreach item='item' index='index' collection='busRoomPayTypes' open=',' separator=',' close=' '>" +
            "SUM(case ogp.payment_method when '${item.id}' then ogp.pay_money else 0 end) as '${item.name}'\n"+
            "</foreach>" +
            "            from bus_room_pay_type_info pty\n" +
            "            left join pos_order_goods_payment ogp on pty.id=ogp.payment_method\n" +
            "<if test='startTime != null'> and ogp.create_time &gt;= '${startTime}' </if>" +
            "<if test='endTime != null'> and ogp.create_time &lt;= '${endTime}' </if>" +
            "            where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and pty.hotel_id = '${hotelId}' </if>" +
            "            UNION ALL\n" +
            "            select max(t.name) as name" +
            "<foreach item='item' index='index' collection='busRoomPayTypes' open=',' separator=',' close=' '>" +
            "SUM(case of.pay_type when '${item.id}' then  of.money else 0 end) as '${item.name}'\n"+
            "</foreach>" +
            "\t\t\t\t\t\tfrom(select id,name from (select 1 as id,'押金' as name \n" +
            "\t\t\t\t\t\tunion select 2 as id,'预收房费' as name \n" +
            "\t\t\t\t\t\tunion select 3 as id,'每日房费' as name\n" +
            "\t\t\t\t\t\tunion select 5 as id,'结账收款' as name\n" +
            "\t\t\t\t\t\tunion select 6 as id,'商品' as name\n" +
            "\t\t\t\t\t  union select 7 as id,'点餐' as name)tmp) t\n" +
            "            left join bus_order_fee of on of.subject_type=t.id\n" +
            "            and of.fee_type=2 and of.preferential_status=2\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and of.hotel_id = '${hotelId}' </if>" +
            "<if test='startTime != null'> and of.create_time &gt;= '${startTime}' </if>" +
            "<if test='endTime != null'> and of.create_time &lt;= '${endTime}' </if>" +
            "            group by t.id</script>")
    public List<HashMap<String, Object>> subjectTypeStatList(@Param("hotelId") String hotelId, @Param("busRoomPayTypes") List<BusRoomPayType> busRoomPayTypes, @Param("startTime") DateTime startTime, @Param("endTime") DateTime endTime);

    /**
     * 按来源各项统计列表
     * @param hotelId
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("<script>SELECT max(a.item_text) as name\n" +
            "            ,ifnull(max(t2.rz_count),0) AS rz_count\n" +
            "            ,sum(ifnull((case t.subject_type when 1 then t.money when 2 then t.money when 3 then t.money when 5 then t.money else 0 end),0)) AS room_money\n" +
            "            ,sum(ifnull((case t.subject_type when 6 then t.money when 7 then t.money else 0 end),0)) AS other_money\n" +
            "            ,sum(ifnull((case t.subject_type when 1 then t.money when 2 then t.money when 3 then t.money when 5 then t.money when 6 then t.money when 7 then t.money else 0 end),0)) AS sum_money\n" +
            "            ,ifnull(round(sum(ifnull((case t.subject_type when 1 then t.money when 2 then t.money when 3 then t.money when 5 then t.money else 0 end),0))/ifnull(max(t2.rz_count),0),2),0) AS room_average_price\n" +
            "\t\t\t\t\t\tfrom bus_dict_item_info a\n" +
            "            LEFT JOIN (select room.customer_source,b.money,b.subject_type,b.room_id from bus_rooms_living_order room\n" +
            "            LEFT JOIN  bus_order_fee b ON room.id=b.living_order_id and b.fee_type=2 and b.preferential_status=2 where 1=1 \n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and room.hotel_id = '${hotelId}' </if>" +
            "<if test='startTime != null'> and room.create_time &gt;= '${startTime}' </if>" +
            "<if test='endTime != null'> and room.create_time &lt;= '${endTime}' </if>" +
            "            ) t\n" +
            "            ON t.customer_source=a.id\n" +
            "            LEFT JOIN (\n" +
            "            select customer_source,count(0) as rz_count from bus_rooms_living_order where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = '${hotelId}' </if>" +
            "<if test='startTime != null'> and create_time &gt;= '${startTime}' </if>" +
            "<if test='endTime != null'> and create_time &lt;= '${endTime}' </if>" +
            "            group by customer_source) t2\n" +
            "            ON t2.customer_source=a.id\n" +
            "            where 1=1 and a.dict_id='1639538915239743490'\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and a.hotel_id = '${hotelId}' </if>" +
            "            GROUP BY a.id</script>")
    public List<HashMap<String, Object>> livingSourceStatList(@Param("hotelId") String hotelId, @Param("startTime") DateTime startTime, @Param("endTime") DateTime endTime);

    /**
     * 按客型各项统计列表
     * @param hotelId
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("<script>SELECT max(a.name) as name\n" +
            "            ,ifnull(max(t2.rz_count),0) AS rz_count\n" +
            "            ,sum(ifnull((case t.subject_type when 1 then t.money when 2 then t.money when 3 then t.money when 5 then t.money else 0 end),0)) AS room_money\n" +
            "            ,sum(ifnull((case t.subject_type when 6 then t.money when 7 then t.money else 0 end),0)) AS other_money\n" +
            "            ,sum(ifnull((case t.subject_type when 1 then t.money when 2 then t.money when 3 then t.money when 5 then t.money when 6 then t.money when 7 then t.money else 0 end),0)) AS sum_money\n" +
            "            ,round(sum(ifnull((case t.subject_type when 1 then t.money when 2 then t.money when 3 then t.money when 5 then t.money else 0 end),0))/ifnull(max(t2.rz_count),0),2) AS room_average_price\n" +
            "\t\t\t\t\t\tfrom (\n" +
            "\t\t\t\t\t\tselect 1 as id,'散客' as name\n" +
            "\t\t\t\t\t\tunion select 2 as id,'会员' as name\n" +
            "\t\t\t\t\t\tunion select 3 as id,'协议单位' as name\n" +
            "\t\t\t\t\t\tunion select 4 as id,'中介' as name\n" +
            "\t\t\t\t\t\t) a\n" +
            "            LEFT JOIN (select room.customer_type,b.money,b.subject_type,b.room_id from bus_rooms_living_order room\n" +
            "            INNER JOIN  bus_order_fee b ON room.id=b.living_order_id and b.fee_type=2 and b.preferential_status=2 \n" +
            "\t\t\t\t\t\twhere 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and room.hotel_id = '${hotelId}' </if>" +
            "<if test='startTime != null'> and room.create_time &gt;= '${startTime}' </if>" +
            "<if test='endTime != null'> and room.create_time &lt;= '${endTime}' </if>" +
            "            ) t\n" +
            "            ON t.customer_type=a.id\n" +
            "            LEFT JOIN (\n" +
            "            select customer_type,count(0) as rz_count from bus_rooms_living_order\n" +
            "\t\t\t\t\t\twhere 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = '${hotelId}' </if>" +
            "            group by customer_type) t2\n" +
            "            ON t2.customer_type=a.id\n" +
            "            GROUP BY a.id</script>")
    public List<HashMap<String, Object>> customerTypeStatList(@Param("hotelId") String hotelId, @Param("startTime") DateTime startTime, @Param("endTime") DateTime endTime);

    /**
     * 按入住类型各项统计列表
     * @param hotelId
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("<script>SELECT max(a.name) as name\n" +
            "            ,ifnull(max(t2.rz_count),0) AS rz_count\n" +
            "            ,sum(ifnull((case t.subject_type when 1 then t.money when 2 then t.money when 3 then t.money when 5 then t.money else 0 end),0)) AS room_money\n" +
            "            ,sum(ifnull((case t.subject_type when 6 then t.money when 7 then t.money else 0 end),0)) AS other_money\n" +
            "            ,sum(ifnull((case t.subject_type when 1 then t.money when 2 then t.money when 3 then t.money when 5 then t.money when 6 then t.money when 7 then t.money else 0 end),0)) AS sum_money\n" +
            "            ,ifnull(round(sum(ifnull((case t.subject_type when 1 then t.money when 2 then t.money when 3 then t.money when 5 then t.money else 0 end),0))/ifnull(max(t2.rz_count),0),2),0) AS room_average_price\n" +
            "\t\t\t\t\t\tfrom (\n" +
            "\t\t\t\t\t\tselect 1 as id,'全天' as name\n" +
            "\t\t\t\t\t\tunion select 2 as id,'钟点房' as name"+
            "\t\t\t\t\t\t) a\n" +
            "            LEFT JOIN (select room.customer_type,room.living_type,b.money,b.subject_type,b.room_id from bus_rooms_living_order room\n" +
            "            INNER JOIN  bus_order_fee b ON room.id=b.living_order_id and b.fee_type=2 and b.preferential_status=2 \n" +
            "\t\t\t\t\t\twhere 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and room.hotel_id = '${hotelId}' </if>" +
            "<if test='startTime != null'> and room.create_time &gt;= '${startTime}' </if>" +
            "<if test='endTime != null'> and room.create_time &lt;= '${endTime}' </if>" +
            "            ) t\n" +
            "            ON t.living_type=a.id\n" +
            "            LEFT JOIN (\n" +
            "            select living_type,count(0) as rz_count from bus_rooms_living_order\n" +
            "\t\t\t\t\t\twhere 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = '${hotelId}' </if>" +
            "            group by living_type) t2\n" +
            "            ON t2.living_type=a.id\n" +
            "            GROUP BY a.id</script>")
    public List<HashMap<String, Object>> livingTypeStatList(@Param("hotelId") String hotelId, @Param("startTime") DateTime startTime, @Param("endTime") DateTime endTime);

    /**
     * 按房间统计当日收入列表
     * @param hotelId
     * @return
     */
    @Select("<script>select b.room_id,room.name as room_name from bus_order_fee b\n" +
            "left join ces_rooms room\n" +
            "on b.room_id=room.id\n" +
            "where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and b.hotel_id = '${hotelId}' </if>" +
            "and b.fee_type=2 and b.preferential_status=2 and b.subject_type!=4\n" +
            "-- and b.subject_type!=5\n" +
            "-- and datediff(b.create_time,now())=0\n" +
            "<if test='startTime != null'> and b.create_time &gt;= '${startTime}' </if>" +
            "<if test='endTime != null'> and b.create_time &lt;= '${endTime}' </if>" +
            "group by b.room_id</script>")
    public List<RoomStatVo> roomStatList(@Param("hotelId") String hotelId, @Param("startTime") DateTime startTime, @Param("endTime") DateTime endTime);

    /**
     * 按房间统计当日收入明细列表
     * @param roomId
     * @return
     */
    @Select("<script>select b.create_time,b.money,\n" +
            "case b.subject_type when 1 then '押金' when 2 then '预收房费' when 3 then '每日房费'  when 5 then '结账收款'  when 6 then '商品' when 7 then '点餐' else '其他' end as subject_type_name,\n" +
            "p.name as pay_name from bus_order_fee b\n" +
            "left join bus_room_pay_type_info p\n" +
            "on b.pay_type=p.id\n" +
            "where 1=1\n" +
            "and b.fee_type=2 and b.preferential_status=2 and subject_type!=4\n" +
            "/* and datediff(b.create_time,now())=0 */\n" +
            "<if test='roomId != null and roomId !=\"\"'> and b.room_id = '${roomId}' </if>" +
            "<if test='startTime != null'> and b.create_time &gt;= '${startTime}' </if>" +
            "<if test='endTime != null'> and b.create_time &lt;= '${endTime}' </if>" +
            "</script>")
    public List<HashMap<String, Object>> roomStatDeatilList(@Param("roomId") String roomId, @Param("startTime") DateTime startTime, @Param("endTime") DateTime endTime);

    /**
     * 按时间统计当日收入明细列表
     * @param hotelId
     * @return
     */
    @Select("<script>select b.create_time,b.money,room.name as room_name,\n" +
            "case b.subject_type when 1 then '押金' when 2 then '预收房费' when 3 then '每日房费'  when 5 then '结账收款'  when 6 then '商品' when 7 then '点餐' else '其他' end as subject_type_name,\n" +
            "p.name as pay_name from bus_order_fee b\n" +
            "left join bus_room_pay_type_info p\n" +
            "on b.pay_type=p.id\n" +
            "left join ces_rooms room\n" +
            "on b.room_id=room.id\n" +
            "where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and b.hotel_id = '${hotelId}' </if>" +
            "and b.fee_type=2 and b.preferential_status=2 and subject_type!=4\n" +
            "/* and datediff(b.create_time,now())=0 */" +
            "<if test='startTime != null'> and b.create_time &gt;= '${startTime}' </if>" +
            "<if test='endTime != null'> and b.create_time &lt;= '${endTime}' </if>" +
            "UNION ALL\n" +
            "select b.create_time,b.pay_money as money,'--' as room_name,'点餐' as subject_type_name,p.name as pay_name from pos_order_goods_payment b\n" +
            "left join bus_room_pay_type_info p\n" +
            "on b.payment_method=p.id\n" +
            "where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and b.hotel_id = '${hotelId}' </if>" +
            "/* and datediff(b.create_time,now())=0 */" +
            "<if test='startTime != null'> and b.create_time &gt;= '${startTime}' </if>" +
            "<if test='endTime != null'> and b.create_time &lt;= '${endTime}' </if>" +
            "</script>")
    public List<HashMap<String, Object>> dayOrderFeeList(@Param("hotelId") String hotelId, @Param("startTime") DateTime startTime, @Param("endTime") DateTime endTime);

    /**
     * 按收款方式统计当日收入明细列表
     * @param payType
     * @return
     */
    @Select("<script>select b.create_time,b.money,\n" +
            "case b.subject_type when 1 then '押金' when 2 then '预收房费' when 3 then '每日房费'  when 5 then '结账收款'  when 6 then '商品' when 7 then '点餐' else '其他' end as subject_type_name,\n" +
            "room.name as room_name from bus_order_fee b\n" +
            "left join ces_rooms room\n" +
            "on b.room_id=room.id\n" +
            "where 1=1\n" +
            "and b.fee_type=2 and b.preferential_status=2 and subject_type!=4\n" +
            "and datediff(b.create_time,now())=0\n" +
            "<if test='payType != null and payType !=\"\"'> and b.pay_type = '${payType}' </if>" +
            "</script>")
    public List<HashMap<String, Object>> dayPayTypeDetailList(@Param("payType") String payType);

    /**
     * 按事项统计当日支出明细列表
     * @param hotelId
     * @return
     */
    @Select("<script>select p.create_time,room.name as room_name,p.money,pt.name as pay_name from bus_order_fee p\n" +
            "left join ces_rooms room\n" +
            "on room.id=p.room_id\n" +
            "left join bus_room_pay_type_info pt\n" +
            "on p.pay_type=pt.id\n" +
            "where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and p.hotel_id = '${hotelId}' </if>" +
            "and p.fee_type=2 and p.preferential_status=2 and p.subject_type=5\n" +
            "and p.custorer_order_remark='结账退款'\n" +
            "and datediff(p.create_time,now())=0</script>")
    public List<HashMap<String, Object>> dayMatterExpendDetailList(@Param("hotelId") String hotelId);

    /**
     * 按时间统计当日支出明细列表
     * @param hotelId
     * @return
     */
    @Select("<script>select b.create_time,b.money,room.name as room_name,\n" +
            "case b.subject_type when 1 then '押金' when 2 then '预收房费' when 3 then '每日房费'  when 5 then '结账退款'  when 6 then '商品' when 7 then '点餐' else '其他' end as subject_type_name,\n" +
            "p.name as pay_name from bus_order_fee b\n" +
            "left join bus_room_pay_type_info p\n" +
            "on b.pay_type=p.id\n" +
            "left join ces_rooms room\n" +
            "on b.room_id=room.id\n" +
            "where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and b.hotel_id = '${hotelId}' </if>" +
            "and b.fee_type=2 and b.preferential_status=2 and b.subject_type=5\n" +
            "and b.custorer_order_remark='结账退款'\n" +
            "and datediff(b.create_time,now())=0</script>")
    public List<HashMap<String, Object>> dayExpendOrderFeeList(@Param("hotelId") String hotelId);

    /**
     * 按退款方式统计当日支出明细列表
     * @param payType
     * @return
     */
    @Select("<script>select b.create_time,b.money,\n" +
            "case b.subject_type when 1 then '押金' when 2 then '预收房费' when 3 then '每日房费'  when 5 then '结账退款'  when 6 then '商品' when 7 then '点餐' else '其他' end as subject_type_name,\n" +
            "room.name as room_name from bus_order_fee b\n" +
            "left join ces_rooms room\n" +
            "on b.room_id=room.id\n" +
            "where 1=1\n" +
            "and b.fee_type=2 and b.preferential_status=2 and b.subject_type=5\n" +
            "and b.custorer_order_remark='结账退款'\n" +
            "and datediff(b.create_time,now())=0\n" +
            "<if test='payType != null and payType !=\"\"'> and b.pay_type = '${payType}' </if>" +
            "</script>")
    public List<HashMap<String, Object>> dayPayTypeExpendDetailList(@Param("payType") String payType);

    /**
     * 收支情况近N天统计
     * @param hotelId
     * @param day 天数
     * @return
     */
    @Select("<script>select sum(money) as money,abs(sum(zc_money)) as zc_money,sum(money)-abs(sum(zc_money)) as jy_money from(\n" +
            "select ifnull(sum(pay_money),0) as money,0 as zc_money from pos_order_goods_payment\n" +
            "where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = '${hotelId}' </if>" +
            "and datediff(create_time,now())>-${day}\n" +
            "UNION ALL\n" +
            "select ifnull(sum(money),0) as money,0 as zc_money from bus_order_fee\n" +
            "where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = '${hotelId}' </if>" +
            "and fee_type=2 and preferential_status=2 and subject_type!=5\n" +
            "and datediff(create_time,now())>-${day}"+
            "UNION ALL\n" +
            "select 0 as money,ifnull(sum(money),0) as zc_money from bus_order_fee\n" +
            "where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = '${hotelId}' </if>" +
            "and fee_type=2 and preferential_status=2 and subject_type=5 and custorer_order_remark='结账退款'\n" +
            "and datediff(create_time,now())>-${day})t</script>")
    public List<HashMap<String, Object>> _7DayFinanceSummary(@Param("hotelId") String hotelId,Integer day);

    /**
     * 收支情况本月统计
     * @param hotelId
     * @return
     */
    @Select("<script>select sum(money) as money,abs(sum(zc_money)) as zc_money,sum(money)-abs(sum(zc_money)) as jy_money from(\n" +
            "select ifnull(sum(pay_money),0) as money,0 as zc_money from pos_order_goods_payment\n" +
            "where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = '${hotelId}' </if>" +
            "and DATE_FORMAT(create_time,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m')\n" +
            "UNION ALL\n" +
            "select ifnull(sum(money),0) as money,0 as zc_money from bus_order_fee\n" +
            "where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = '${hotelId}' </if>" +
            "and fee_type=2 and preferential_status=2 and subject_type!=5\n" +
            "and DATE_FORMAT(create_time,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m')"+
            "UNION ALL\n" +
            "select 0 as money,ifnull(sum(money),0) as zc_money from bus_order_fee\n" +
            "where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = '${hotelId}' </if>" +
            "and fee_type=2 and preferential_status=2 and subject_type=5 and custorer_order_remark='结账退款'\n" +
            "and DATE_FORMAT(create_time,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m'))t</script>")
    public List<HashMap<String, Object>> monthFinanceSummary(@Param("hotelId") String hotelId);

    /**
     * 收支情况本年统计
     * @param hotelId
     * @return
     */
    @Select("<script>select sum(money) as money,abs(sum(zc_money)) as zc_money,sum(money)-abs(sum(zc_money)) as jy_money from(\n" +
            "select ifnull(sum(pay_money),0) as money,0 as zc_money from pos_order_goods_payment\n" +
            "where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = '${hotelId}' </if>" +
            "and DATE_FORMAT(create_time,'%Y')=DATE_FORMAT(CURDATE(),'%Y')\n" +
            "UNION ALL\n" +
            "select ifnull(sum(money),0) as money,0 as zc_money from bus_order_fee\n" +
            "where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = '${hotelId}' </if>" +
            "and fee_type=2 and preferential_status=2 and subject_type!=5\n" +
            "and DATE_FORMAT(create_time,'%Y')=DATE_FORMAT(CURDATE(),'%Y')"+
            "UNION ALL\n" +
            "select 0 as money,ifnull(sum(money),0) as zc_money from bus_order_fee\n" +
            "where 1=1\n" +
            "<if test='hotelId != null and hotelId !=\"\"'> and hotel_id = '${hotelId}' </if>" +
            "and fee_type=2 and preferential_status=2 and subject_type=5 and custorer_order_remark='结账退款'\n" +
            "and DATE_FORMAT(create_time,'%Y')=DATE_FORMAT(CURDATE(),'%Y'))t</script>")
    public List<HashMap<String, Object>> yearFinanceSummary(@Param("hotelId") String hotelId);
}
